﻿CREATE PROCEDURE [dbo].[proc_Customer_GetShiftList]
	(
		@DepartmentId int,
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex int
	)
AS
begin
	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepartmentId
--	declare @indextable table(id int identity(1,1),nid int)
--	;WITH ChildDepartment(ID,DName,PId) AS
--	(
--		SELECT ID,DName,ParentID FROM department WHERE id=@departmentid
--	    
--		UNION ALL
--		SELECT department.ID,department.DName,department.ParentID FROM ChildDepartment AS d
--		INNER JOIN department ON d.ID = department.ParentID
--	)
--	
--	insert into @indextable(nid) select Id from ChildDepartment;

	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY ProtectDate Desc, C.Id DESC)AS Row,
		C.Id,C.CustomerId,C.CustomerName,Project,
		isnull(C.ProtectDate,'1900-01-01') as ProtectDate,
	    (select d.DName from Department d Where d.id=(select e.departmentid from employee e where e.username=C.ProtectName)) as DepartmentName,
		(select e.ename from employee e where e.username=C.ProtectName) as ProtectName,
		ISNULL((Select Title From Customer_Industry Where Id=C.IndustryOneId),'...') As IndustryOne,
		ISNULL((Select Title From Customer_Industry Where Id=C.IndustryTwoId),'...') As IndustryTwo,
		ISNULL((Select Title From Customer_Source Where Id=C.SourceId),'...') As Source,
		ISNULL((Select Title From Customer_Type Where Id=C.TypeId),'...') As Type
		From Customer C 
		where
			(select Top 1 e.departmentid from employee e where e.username=C.ProtectName and e.state=5) in(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T) 
			 and ((select Top 1 e.ename from employee e where e.username=C.ProtectName and e.state=5) like  '%'+@SText+'%' or C.CustomerName like '%'+@SText+'%')
			 and C.ProtectId=2
		)
		Select *,(Select Count(0) From List) As RecordCount From list Where Row between @StartIndex and @EndIndex  Order By row
end
	RETURN
